<?php

class Angsuran_model extends Model {

	var $data;
	var $dataJurnal;
	var $dataJurnaldetails1;
	var $dataJurnaldetails2;
	var $dabung;
	var $dabung_transaksi;
	
	function Angsuran_model()
	{
		parent::Model();
	}
	
	function set_no_rekening_id($no_rekening_id)
	{
		$this->db->where('angsuran.no_rekening_id', $no_rekening_id);
	}

	function get_all_data(){
		$this->db->select('angsuran.*, pinjaman.no_rekening, jenis_pinj.id_jenis_pinj, anggota.nama_lengkap' );
		$this->db->from('angsuran');
		$this->db->join('pinjaman', 'angsuran.no_rekening_id=pinjaman.id', 'INNER');
		$this->db->join('jenis_pinj', 'angsuran.jenis_pinj=jenis_pinj.id', 'INNER');
		$this->db->join('anggota', 'angsuran.anggota_id=anggota.id_anggota', 'INNER');
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
			return $query->result();
		}
		else
		{
			return FALSE;
		}
	}
	
	//SUM(angsuran.biaya) as saldo_simp
	
	function get_last_id(){
		$query = $this->db->query('select * from angsuran order by id desc limit 1 ');
		if ($query->num_rows() > 0)
		{
			return $query->row();
		}
		else
		{
			return FALSE;
		}
	}

	function get_data_by_id($id){
		$this->db->select('angsuran.id, angsuran.no_transaksi, angsuran.no_rekening_id, pinjaman.no_rekening, angsuran.spk_no, angsuran.tgl_reg, angsuran.biaya, angsuran.tgl_mulai, angsuran.tgl_selesai, angsuran.jenis_pinj, angsuran.keterangan, angsuran.saldo_sblm, anggota.nama_lengkap as nama_anggota, jenis_pinj.id_jenis_pinj, angsuran.sandi_simp, pinjaman.no_rekening, angsuran.waktu_post, angsuran.anggota_id');
		$this->db->from('angsuran');
		$this->db->join('pinjaman', 'angsuran.no_rekening_id=pinjaman.id', 'INNER');
		$this->db->join('jenis_pinj', 'angsuran.jenis_pinj=jenis_pinj.id', 'INNER');
		$this->db->join('anggota', 'pinjaman.anggota_id=anggota.id_anggota', 'INNER');
		$this->db->where('angsuran.id', $id);
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
			return $query->row_array();
		}
		else
		{
			return FALSE;
		}
	}
	
	function get_anggota_by_id($id){
		$this->db->select('pinjaman.anggota_id');
		$this->db->from('angsuran');
		$this->db->join('pinjaman', 'angsuran.no_rekening_id=pinjaman.id', 'INNER');
		$this->db->join('anggota', 'pinjaman.anggota_id=anggota.id_anggota', 'INNER');
		$this->db->where('angsuran.id', $id);
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
			return $query->row_array();
		}
		else
		{
			return FALSE;
		}
	}
	
	function get_saldo_by_id($id){
		
		$my_nci = $this->get_anggota_by_id($id);
	
		/*
		$this->db->select('SUM(angsuran.biaya) as saldo_simp');
		$this->db->from('angsuran');
		//$this->db->join('pinjaman', 'angsuran.no_rekening_id=pinjaman.id', 'INNER');
		//$this->db->join('anggota', 'pinjaman.anggota_id=anggota.id_anggota', 'INNER');
		
		$this->db->where('angsuran.jenis_pinj', '1');
		$this->db->where('angsuran.anggota_id', $my_nci['anggota_id']);
		//$this->db->group_by('angsuran.id');
		*/
		$this->db->select('SUM(angsuran.biaya) as saldo_simp');
		$this->db->from('angsuran');
		
		$this->db->where('angsuran.anggota_id', $my_nci['anggota_id']);
		
		$query = $this->db->get();
		if ($query->num_rows() > 0)
		{
			return $query->row_array();
		}
		else
		{
			return FALSE;
		}
	}

	function get_last_id_jurnal(){
		$query = $this->db->query('select * from jurnal order by id desc limit 1 ');
		if ($query->num_rows() > 0)
		{
			return $query->row();
		}
		else
		{
			return FALSE;
		}
	}
	
	function fill_data()
	{				
	
		$biaya = $this->input->post('biaya');
		$biaya = str_replace(",00", "", $biaya);
		$biaya = str_replace("Rp", "", $biaya);
		$biaya = str_replace(" ", "", $biaya);
		$biaya = str_replace(".", "", $biaya);
		
		if($this->input->post('sandi_simp') == 2){
			//echo 'sayang :*';
			$biaya = $biaya * -1; 
		}
		
		$pinjaman = $this->input->post('pinjaman2');
		$pinjaman = str_replace (" ", "", $pinjaman);
		$pinjaman = str_replace (".", "", $pinjaman);
		$pinjaman = str_replace ("Rp", "", $pinjaman);
				
		$jenis_pinj = $this->input->post('jenis_pinj2');
		
		if($jenis_pinj == 'Harian'){
			$jenis_pinj = 1;
		}else if($jenis_pinj == 'Mingguan'){
			$jenis_pinj = 2;
		}else if($jenis_pinj == 'Bulanan'){
			$jenis_pinj = 3;			
		}else if($jenis_pinj == 'Gadean'){
			$jenis_pinj = 4;			
		}else if($jenis_pinj == 'P3KUM'){
			$jenis_pinj = 5;			
		}
		$this->data = array(
			'no_transaksi' => $this->input->post('no_transaksi'),
			'no_rekening_id' => $this->input->post('no_rekeningID'),
			'spk_no' => $this->input->post('nomorspk'),			
			'tgl_reg' => $this->input->post('tgl_reg'),
			'saldo_sblm' => $pinjaman,
			'anggota_id' => $this->input->post('anggota_id'),
			'angsuran_ke' => $this->input->post('angsuran_ke'),
			'total_angsuran' => $this->input->post('total_angsuran'),
			'biaya' => $biaya,
			'saldo_skrg' => $pinjaman - $biaya,
			
			'tgl_mulai' => $this->input->post('from'),
			'tgl_selesai' => $this->input->post('to'),
			'sandi_simp' => $this->input->post('sandi_simp'),
			//'jenis_pinj' => $this->input->post('jenis_pinj'),			
			//'jenis_pinj' => '1',			
			'jenis_pinj' => $jenis_pinj,
			'keterangan' => $this->input->post('keterangan'),
			'waktu_post' => date("Y-m-d H:i:s")
		);		
		
		$id_jurnal = 1;
		
		if($this->get_last_id_jurnal() == FALSE){			
			$id_jurnal = 1;
		}else{
			$arr = $this->get_last_id_jurnal();
			$id_jurnal = $arr->id+1;
		}
		
		/*
		if ($this->input->post('jenis_pinj')==1){ //sukarela
			$this->dataJurnal = array(
			'no' => $id_jurnal,//$id_jurnal->no+1,
			'tgl' => $this->input->post('tgl_reg'),
			'f_id' => '1',
			'keterangan' => 'pinjaman sukarela (tabungan)',
			'login_id' => $this->session->userdata('SESS_USER_ID'),
			'waktu_post' => date("Y-m-d H:i:s")
			);		
			
			$this->dataJurnaldetails1 = array(
				'jurnal_id' => $id_jurnal,
				'item' => '1',
				'akun_id' => '2', //kas
				'debit_kredit' => '1',
				'nilai' => $biaya
			);
			$this->dataJurnaldetails2 = array(
				'jurnal_id' => $id_jurnal,//$id_jurnal->id+1,
				'item' => '2',
				'akun_id' => '28', //pinjaman sukarela
				'debit_kredit' => '0',
				'nilai' => $biaya
			);
		}else if ($this->input->post('jenis_pinj')==2){ //pokok
			$this->dataJurnal = array(
				'no' => $id_jurnal,
				'tgl' => $this->input->post('tgl_reg'),
				'f_id' => '1',
				'keterangan' => 'pinjaman pokok',
				'login_id' => $this->session->userdata('SESS_USER_ID'),
				'waktu_post' => date("Y-m-d H:i:s")
			);		
			
			$this->dataJurnaldetails1 = array(
				'jurnal_id' => $id_jurnal,
				'item' => '1',
				'akun_id' => '2', //kas
				'debit_kredit' => '1',
				'nilai' => $biaya
			);
			$this->dataJurnaldetails2 = array(
				'jurnal_id' => $id_jurnal,
				'item' => '2',
				'akun_id' => '24', //pinjaman pokok
				'debit_kredit' => '0',
				'nilai' => $biaya
			);
		}else if ($this->input->post('jenis_pinj')==3){ //wajib
			$this->dataJurnal = array(
				'no' => $id_jurnal,
				'tgl' => $this->input->post('tgl_reg'),
				'f_id' => '1',
				'keterangan' => 'pinjaman wajib',
				'login_id' => $this->session->userdata('SESS_USER_ID'),
				'waktu_post' => date("Y-m-d H:i:s")
			);
			
			$this->dataJurnaldetails1 = array(
				'jurnal_id' => $id_jurnal,
				'item' => '1',
				'akun_id' => '2', //kas
				'debit_kredit' => '1',
				'nilai' => $biaya
			);
			$this->dataJurnaldetails2 = array(
				'jurnal_id' => $id_jurnal,
				'item' => '2',
				'akun_id' => '25', //pinjaman wajib
				'debit_kredit' => '0',
				'nilai' => $biaya
			);
		}		
		*/
	}
	
	function get_last_transaksi_for_bunga()
	{
		$query = $this->db->query('select * from angsuran order by id desc limit 1 ');
		if ($query->num_rows() > 0)
		{
			return $query->result();
		}
		else
		{
			return FALSE;
		}
	}
	function fill_dabung() // data bunga
	{				
		/*
		if($this->input->post('sandi_simp') == 2){
			//echo 'sayang :*';
			$biaya = $this->input->post('biaya') * -1; 
		}else{
		}
		*/
		
		
		//$biaya = "772200"; 		
		
		$no = 0;
		$no_transaksi = 0;
		$no_rekening_id = 0;
		$biaya = 0;
		$saldo_sblm = 0;
		$anggota_id = 0;
		
		$arrData = $this->get_last_transaksi_for_bunga();
		if($arrData)
		{		
			foreach ($arrData as $row)
			{
				//$this->dabung = $row->saldo_skrg;
				$no_transaksi = $this->formatIdangsuran($row->no_transaksi + 1);
				$no_rekening_id = $row->no_rekening_id;
				$anggota_id = $row->anggota_id;
				$biaya = $row->saldo_skrg * 0.1;
				$saldo_sblm = $row->saldo_skrg;
				$no++;
			}
		}
		//echo 'cikan : ' . $no;
		/*
		$biaya = $this->input->post('biaya');
		$biaya = str_replace(",00", "", $biaya);
		$biaya = str_replace("Rp", "", $biaya);
		$biaya = str_replace(" ", "", $biaya);
		$biaya = str_replace(".", "", $biaya);		
		
		if($this->input->post('sandi_simp') == 2){
			//echo 'sayang :*';
			$biaya = $biaya * -1; 
		}
		*/		
		$sandi_simp = 3;//bunga
		$this->dabung = array(
			'no_transaksi' => $no_transaksi, //$this->input->post('no_transaksi'),
			'no_rekening_id' => $no_rekening_id,//$this->input->post('no_rekeningID'),
			'spk_no' => $this->input->post('nomorspk'),			
			'tgl_reg' => date("Y-m-d H:i:s"),//$this->input->post('tgl_reg'),
			'saldo_sblm' => $saldo_sblm,
			'anggota_id' => $anggota_id,//$this->input->post('anggota_id'),
			'biaya' => $biaya,
			'saldo_skrg' => $biaya + $saldo_sblm,
			'tgl_mulai' => $this->input->post('from'),
			'tgl_selesai' => $this->input->post('to'),
			'sandi_simp' => $sandi_simp,
			'jenis_pinj' => 1,//sukarela		
			'keterangan' => 'bunga',//,
			'waktu_post' => date("Y-m-d H:i:s")
		);				
		$id_jurnal = 1;
		
		if($this->get_last_id_jurnal() == FALSE){
			$id_jurnal = 1;
		}else{
			$arr = $this->get_last_id_jurnal();
			$id_jurnal = $arr->id+1;
		}		
			$this->dataJurnal = array(
			'no' => $id_jurnal,//$id_jurnal->no+1,
			'tgl' => $this->input->post('tgl_reg'),
			'f_id' => '1',
			'keterangan' => 'pinjaman sukarela (tabungan)',
			'login_id' => $this->session->userdata('SESS_USER_ID'),
			'waktu_post' => date("Y-m-d H:i:s")
			);		
			
			$this->dataJurnaldetails1 = array(
				'jurnal_id' => $id_jurnal,
				'item' => '1',
				'akun_id' => '2', //kas
				'debit_kredit' => '1',
				'nilai' => $biaya
			);
			$this->dataJurnaldetails2 = array(
				'jurnal_id' => $id_jurnal,//$id_jurnal->id+1,
				'item' => '2',
				'akun_id' => '28', //pinjaman sukarela
				'debit_kredit' => '0',
				'nilai' => $biaya
			);	
	}
	
	//Check for duplicate login ID
	function check_name($id = '')
	{
		$this->db->where('no_transaksi', $this->data['no_transaksi']);
		if($id != '') $this->db->where('id !=', $id);
		$query = $this->db->get('angsuran');

		if ($query->num_rows() > 0)
		{
			return FALSE;
		}
		else
		{
			return TRUE;
		}
	}	

	function insert_data()
	{
		$insert = $this->db->insert('angsuran', $this->data);
		//$this->db->insert('jurnal', $this->dataJurnal);		
		
		//$this->db->insert('jurnal_detail', $this->dataJurnaldetails1);
		//$op = ($this->dataJurnaldetails1['debit_kredit']) ? '+' : '-';		
		//$this->db->query('UPDATE akun SET saldo = saldo'.$op.$this->dataJurnaldetails1['nilai'].' WHERE id = '.$this->dataJurnaldetails1['akun_id']);
		
		//$this->db->insert('jurnal_detail', $this->dataJurnaldetails2);
		//$op = ($this->dataJurnaldetails2['debit_kredit']) ? '+' : '-';		
		//$this->db->query('UPDATE akun SET saldo = saldo'.$op.$this->dataJurnaldetails2['nilai'].' WHERE id = '.$this->dataJurnaldetails2['akun_id']);
		
				
		return $insert;
	}

	function update_data($id)
	{
		$this->db->where('id', $id);
		$update = $this->db->update('angsuran', $this->data);
		
		//$this->db->where('id', $id);
		//$this->db->update('jurnal_detail ', $this->dataJurnaldetails1);
		
		$op = ($this->dataJurnaldetails1['debit_kredit']) ? '+' : '-';		
		$this->db->query('UPDATE jurnal_detail SET nilai = '.$op.$this->data['biaya'].' WHERE jurnal_id = '.$id);
		
		//$this->db->query('UPDATE akun SET saldo = saldo'.$op.$this->data['biaya'].' WHERE id = '.$this->dataJurnaldetails1['akun_id']);
		
		//$op = ($this->dataJurnaldetails2['debit_kredit']) ? '+' : '-';
		//$this->db->query('UPDATE akun SET saldo = saldo'.$op.$this->data['biaya'].' WHERE id = '.$this->dataJurnaldetails2['akun_id']);
		
		//$op = ($this->dataJurnaldetails2['debit_kredit']) ? '+' : '-';		
		//$this->db->query('UPDATE jurnal_detail SET nilai = nilai'.$op.$this->dataJurnaldetails2['nilai'].' WHERE jurnal_id = '.$id);
				
		return $update;
	}

	function delete_data($id){
		$this->db->where('id', $id);
		$delete = $this->db->delete('angsuran');
		
		//echo 'oi!!';
		/*
		$query = $this->db->query("SELECT no_transaksi FROM angsuran WHERE id=".$id);
		if ($query->num_rows() > 0)
		{
			$row = $query->row_array();
			$no_transaksi = $row['no_transaksi'];			
		}
		*/
		
		$this->db->query('DELETE FROM bunga WHERE bunga_id = '.$id);
		
		//$this->db->query('DELETE FROM jurnal  WHERE jurnal_id = '.$id);
		$op = ($this->dataJurnaldetails1['debit_kredit']) ? '+' : '-';		
		$this->db->query('UPDATE jurnal_detail SET nilai = 0 WHERE jurnal_id = '.$id);
				
		return $delete;
	}
	
	function get_rek()
	{
		$query = $this->db->query("select *, sum(biaya) as 'total' from angsuran group by no_rekening_id ");
		if ($query->num_rows() > 0)
		{
			return $query->result();
		}
		else
		{
			return FALSE;
		}
	}
	
	function duplicate_bunga($tgl_bunga, $no_rekening_id)
	{
		$query = $this->db->query("SELECT * FROM bunga WHERE tgl_bunga='$tgl_bunga' AND no_rekening_id='$no_rekening_id'");
		if ($query->num_rows() > 0)
		{
			return TRUE;
		}
		else
		{
			return FALSE;
		}
	}
	
	function get_tgl_feb_sukarela()
	{
		$query = $this->db->query("SELECT tgl_feb FROM jenis_pinj WHERE id='1'");
		if ($query->num_rows() > 0)
		{
			$row = $query->row_array();
			return $row['tgl_feb'];
		}
		else
		{
			return FALSE;
		}
	}
	
	function get_tgl_other_sukarela()
	{
		$query = $this->db->query("SELECT tgl_other FROM jenis_pinj WHERE id='1'");
		if ($query->num_rows() > 0)
		{
			$row = $query->row_array();
			return $row['tgl_other'];
		}
		else
		{
			return FALSE;
		}
	}
	
	function get_suku_bunga_sukarela()
	{
		$query = $this->db->query("SELECT bunga FROM jenis_pinj WHERE id='1'");
		if ($query->num_rows() > 0)
		{
			$row = $query->row_array();
			return $row['bunga'];
		}
		else
		{
			return FALSE;
		}
	}
	
	function get_last_transaksi_id()
	{
		$query = $this->db->query("SELECT id FROM angsuran order by id desc limit 1");
		if ($query->num_rows() > 0)
		{
			$row = $query->row_array();
			return $row['id'];
		}
		else
		{
			return FALSE;
		}
	}
	
	function hit_bunga(){
		
		$tgl_jatuh = 0;
		
		if(date('m') == 2){
			$tgl_jatuh = $this->get_tgl_feb_sukarela();
		}else{
			$tgl_jatuh = $this->get_tgl_other_sukarela();
		}
		
		if(date("j") == $tgl_jatuh){
			//$this->fill_dabung();
			//$this->update_bunga();
			
			//$data['cobacuy'] = $this->angsuran_model->update_bunga();
			//$arrData = $this->check_available_bunga(date("m"));
			$no = 0;
			$arrData = $this->get_rek();
			$no_transaksi = '';
			$bunga_id = 0;
			
			if($arrData)
			{		
				foreach ($arrData as $row)
				{
					//$this->dabung = $row->saldo_skrg;
					//$no_transaksi = $this->formatIdangsuran($row->no_transaksi + 1);
					//$no_transaksi = $this->formatIdangsuran($row->no_transaksi);
					$bunga_id = $this->get_last_transaksi_id() + 1;
					$no_rekening_id = $row->no_rekening_id;
					$anggota_id = $row->anggota_id;
					$biaya = $row->total * $this->get_suku_bunga_sukarela();
					$saldo_sblm = $row->total;
					$no++;
					
					$arrTran = $this->get_last_transaksi_for_bunga();
					if($arrTran)
					{		
						foreach ($arrTran as $rowt)
						{
							//$this->dabung = $row->saldo_skrg;
							$no_transaksi = $this->formatIdangsuran($rowt->no_transaksi + 1);
						}
					}
					
				
					$this->dabung = array(						
						'bunga_id' => $bunga_id, 
						'tgl_bunga' => date("Y-m-d"),
						'no_transaksi' => $no_transaksi, 
						'no_rekening_id' => $no_rekening_id,
						'saldo' => $saldo_sblm,
						'brp_hari' => '12',
						'bunga' => $biaya
					);			
					
					//$no_transaksi = $this->formatIdangsuran($row->no_transaksi + 1);					
					//$saldo_sblm = $row->saldo_skrg;					
					$sandi_simp = 3;//bunga
					
					$this->dabung_transaksi = array(
						'no_transaksi' => $no_transaksi, //$this->input->post('no_transaksi'),
						'no_rekening_id' => $no_rekening_id,//$this->input->post('no_rekeningID'),
						'spk_no' => $this->input->post('nomorspk'),			
						'tgl_reg' => date("Y-m-d H:i:s"),//$this->input->post('tgl_reg'),
						'saldo_sblm' => $saldo_sblm,
						'anggota_id' => $anggota_id,//$this->input->post('anggota_id'),
						'biaya' => $biaya,
						'saldo_skrg' => $biaya + $saldo_sblm,
						'sandi_simp' => $sandi_simp,
						'jenis_pinj' => 1,//sukarela		
						'keterangan' => 'bunga',//,
						'waktu_post' => date("Y-m-d H:i:s")
					);
					$id_jurnal = 1;
					
					if($this->get_last_id_jurnal() == FALSE){
						$id_jurnal = 1;
					}else{
						$arr = $this->get_last_id_jurnal();
						$id_jurnal = $arr->id+1;
					}		
						$this->dataJurnal = array(
						'no' => $id_jurnal,//$id_jurnal->no+1,
						'tgl' => $this->input->post('tgl_reg'),
						'f_id' => '1',
						'keterangan' => 'pinjaman sukarela (tabungan)',
						'login_id' => $this->session->userdata('SESS_USER_ID'),
						'waktu_post' => date("Y-m-d H:i:s")
						);		
						
						$this->dataJurnaldetails1 = array(
							'jurnal_id' => $id_jurnal,
							'item' => '1',
							'akun_id' => '2', //kas
							'debit_kredit' => '1',
							'nilai' => $biaya
						);
						$this->dataJurnaldetails2 = array(
							'jurnal_id' => $id_jurnal,//$id_jurnal->id+1,
							'item' => '2',
							'akun_id' => '28', //pinjaman sukarela
							'debit_kredit' => '0',
							'nilai' => $biaya
						);	
						
					if($row->total > 100000) {
						if($this->duplicate_bunga(date("Y-m-d"), $no_rekening_id) == FALSE){
							$this->update_bunga();
						}
					}
				}
				//return 'ada';
				//return $no;
				//return $no_transaksi;
			}
		}else{
			//$data['cobacuy'] = 'under';			
			//return 'noin';
			//return $this->get_tgl_feb_sukarela();			
		}
		//return $this->get_suku_bunga_sukarela();
	}
	function update_bunga(){
		$insert = $this->db->insert('bunga', $this->dabung);
		$insert = $this->db->insert('angsuran', $this->dabung_transaksi);
		$this->db->insert('jurnal', $this->dataJurnal);		
		
		$this->db->insert('jurnal_detail', $this->dataJurnaldetails1);
		$op = ($this->dataJurnaldetails1['debit_kredit']) ? '+' : '-';		
		$this->db->query('UPDATE akun SET saldo = saldo'.$op.$this->dataJurnaldetails1['nilai'].' WHERE id = '.$this->dataJurnaldetails1['akun_id']);
		
		$this->db->insert('jurnal_detail', $this->dataJurnaldetails2);
		$op = ($this->dataJurnaldetails2['debit_kredit']) ? '+' : '-';		
		$this->db->query('UPDATE akun SET saldo = saldo'.$op.$this->dataJurnaldetails2['nilai'].' WHERE id = '.$this->dataJurnaldetails2['akun_id']);
						
		return $insert;
		
	}
	
	function formatIdangsuran($id){		
		if (strlen($id) == 1)
		{
			return '000000' . $id;
		}
		else if (strlen($id) == 2)
		{
			return '00000' . $id;
		}
		else if (strlen($id) == 3)
		{
			return '0000' . $id;
		}
		else if (strlen($id) == 4)
		{
			return '000' . $id;
		}
		else if (strlen($id) == 5)
		{
			return '00' . $id;
		}
		else if (strlen($id) == 6)
		{
			return '0' . $id;
		}
		else {
			return $id;
		}
	}
}


/* End of file angsuran_model.php */
/* Location: ./application/models/angsuran_model.php */
